﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.IRepository.Basic_information;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Basic_information
{
    [Dependency(ServiceLifetime.Scoped)]
    public class VehiclesRepository : IVehiclesRepository
    {

        private MyDbContext myDbContext { get; set; }

        public VehiclesRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public List<Vehicles_ment> GetVehicles(string date1, string date2, string value2)
        {
            
            string sql = "SELECT Vc_id, Vc_phone, Vc_model, Vc_length, Vc_color, Vc_brand, Vc_pas_cap, Vc_vol_cap, Vc_wt_cap, Vc_rel_date, Vc_veh_age, Vc_manu, V.Dv_id, Vc_status, Vc_photo, Vc_remarks, V.Create_by, V.Create_time, V.Update_by, V.Update_time, V.Is_del FROM dbo.Vehicles_ment V, dbo.Drivers_ment D WHERE V.Is_del=0 AND V.Dv_id=D.Dv_id";
            if (date1 != null && date2 != null)
            {
                sql += $" AND (V.Create_time>='{date1}' AND V.Create_time<='{date2}') ";
            }
            if (value2 != null && value2 != "")
            {
                sql += $" AND (Dv_name LIKE '%{value2}%')";
            }
            return myDbContext.Database.GetDbConnection().Query<Vehicles_ment>(sql).ToList();
        }

        public int GetCount(string date1, string date2, string value2)
        {
            string sql = "SELECT Vc_id, Vc_phone, Vc_model, Vc_length, Vc_color, Vc_brand, Vc_pas_cap, Vc_vol_cap, Vc_wt_cap, Vc_rel_date, Vc_veh_age, Vc_manu, V.Dv_id, Vc_status, Vc_photo, Vc_remarks, V.Create_by, V.Create_time, V.Update_by, V.Update_time, V.Is_del FROM dbo.Vehicles_ment V, dbo.Drivers_ment D WHERE V.Is_del=0 AND V.Dv_id=D.Dv_id";
            if (date1 != null && date2 != null)
            {
                sql += $" AND (Vc_rel_date>='{date1}' AND Vc_rel_date<='{date2}') ";
            }
            if (value2 != null && value2 != "")
            {
                sql += $" AND (Dv_name LIKE '%{value2}%')";
            }
            return myDbContext.Database.GetDbConnection().Query<Vehicles_ment>(sql).Count();
        }

        public int DelVehicles(int id)
        {
            string sql = $"UPDATE dbo.Vehicles_ment SET Is_del=1 WHERE Vc_id={id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int AddVehicles(Vehicles_ment vehicles)
        {
            string sql = @$"INSERT INTO dbo.Vehicles_ment(Vc_phone, Vc_model, Vc_length, Vc_color, Vc_brand, Vc_pas_cap, Vc_vol_cap, Vc_wt_cap, Vc_rel_date, Vc_veh_age, Vc_manu, Dv_id, Vc_status, Vc_photo, Vc_remarks, Create_by, Create_time, Is_del) 
            VALUES('{vehicles.Vc_phone}', '{vehicles.Vc_model}', '{vehicles.Vc_length}', '{vehicles.Vc_color}', '{vehicles.Vc_brand}', '{vehicles.Vc_pas_cap}', '{vehicles.Vc_vol_cap}', '{vehicles.Vc_wt_cap}', '{vehicles.Vc_rel_date}', '{vehicles.Vc_veh_age}', '{vehicles.Vc_manu}', '{vehicles.Dv_id}', '{vehicles.Vc_status}', '{vehicles.Vc_photo}', '{vehicles.Vc_remarks}', '{vehicles.Create_by}', '{vehicles.Create_time}', '{vehicles.Is_del}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public Vehicles_ment GetById(int id)
        {
            string sql = $"SELECT * FROM dbo.Vehicles_ment WHERE Is_del=0 AND Vc_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Vehicles_ment>(sql).FirstOrDefault();
        }

        public int UpdVehicles(Vehicles_ment vehicles)
        {
            string sql = $@"UPDATE dbo.Vehicles_ment SET Vc_phone='{vehicles.Vc_phone}', Vc_model='{vehicles.Vc_model}', Vc_length='{vehicles.Vc_length}', Vc_color='{vehicles.Vc_color}', Vc_brand='{vehicles.Vc_brand}', Vc_pas_cap='{vehicles.Vc_pas_cap}', Vc_vol_cap='{vehicles.Vc_vol_cap}', Vc_wt_cap='{vehicles.Vc_wt_cap}', Vc_rel_date='{vehicles.Vc_rel_date}', Vc_veh_age='{vehicles.Vc_veh_age}', Vc_manu='{vehicles.Vc_manu}', Dv_id='{vehicles.Dv_id}', Vc_status='{vehicles.Vc_status}', Vc_photo='{vehicles.Vc_photo}', Vc_remarks='{vehicles.Vc_remarks}', Update_by='{vehicles.Update_by}', Update_time='{vehicles.Update_time}'
            WHERE Is_del=0 AND Vc_id='{vehicles.Vc_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

    }
}
